package leechapi.proc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Set;

import org.h2.tools.SimpleResultSet;

public class Historique {

	public static ResultSet histo(Connection conn,int siteId) throws SQLException {
		SimpleResultSet rs = new SimpleResultSet();
		Date[] dates = getLeechDates(conn,siteId);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		rs.addColumn("CODE_POSTAL", Types.VARCHAR, 255, 0);
		for (Date date : dates) {
			rs.addColumn("" + sdf.format(date), Types.INTEGER, 10, 0);
		}
		ajouteCP(35830, "Betton", rs, conn, dates,siteId);
		ajouteCP(35510, "Cesson Sévigné", rs, conn, dates,siteId);		
		ajouteCP(35135, "Chantepie", rs, conn, dates,siteId);
		ajouteCP(35410, "Chateaugiron", rs, conn, dates,siteId);
		ajouteCP(35340, "Liffré", rs, conn, dates,siteId);
		ajouteCP(35740, "Pacé", rs, conn, dates,siteId);		
		ajouteCP(35760, "Saint-Grégoire", rs, conn, dates,siteId);
		ajouteCP(-1, "Ille-et-Vilaine", rs, conn, dates,siteId);
		return rs;
	}

	private static void ajouteCP(int codePostal, String nomCommune, SimpleResultSet rs, Connection conn, Date[] dates,int siteId) throws SQLException {
		ArrayList<Object> objects = new ArrayList<Object>();
		objects.add(nomCommune);
		Statement s = conn.createStatement();
		ResultSet rrs = null;
		if (codePostal > 0) {
			rrs = s.executeQuery("select avg(prix) from offre where site = "+siteId+" and code_postal like '%" + codePostal + "%' and date in (select distinct date from offre) group by date order by date");
		} else {
			rrs = s.executeQuery("select avg(prix) from offre where site = "+siteId+" and date in (select distinct date from offre) group by date order by date");
		}
		while (rrs.next()) {
			objects.add(rrs.getInt(1));
		}
		rs.addRow(objects.toArray(new Object[objects.size()]));
	}

	private static Date[] getLeechDates(Connection conn,int siteId) throws SQLException {
		ArrayList<Date> dates = new ArrayList<Date>();
		ArrayList<Date> finalDates = new ArrayList<Date>();
		Statement s = conn.createStatement();
		ResultSet rs = s.executeQuery("select distinct date from offre where site = "+siteId+" order by date asc");
		while (rs.next()) {
			dates.add(rs.getDate(1));
		}
		Set<Integer> moisPresents = new HashSet<Integer>();
		// une seule date par mois
		for (Date date : dates) {
			Calendar cal = GregorianCalendar.getInstance();
			cal.setTime(date);
			int mois = cal.get(GregorianCalendar.MONTH);
			if (!moisPresents.contains(mois)) {
				moisPresents.add(mois);
				finalDates.add(date);
			}
		}
		return finalDates.toArray(new Date[finalDates.size()]);
	}
}